package ks.server.persist;

import java.net.URL;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.UUID;

import ks.managers.plugin.*;
import ks.managers.icon.*;
import ks.managers.user.User;

/**
 * Provides persistent database access to managers.
 * 
 * @author George Heineman
 * @author Jennifer Hunt
 * @author Harry Moreno
 * @author Christopher Page
 */
public class Database {
	/** Hard-coded database access information */
	/**
	 * Should you choose an alternate MYSQL database system, you will need to
	 * update these credentials. 
	 *Plugin
	 * This information must be kept reasonably private
	 */
	private static final String DB_SERVER   = "mysql.wpi.edu";
	private static final String DB_USER     = "tcss_admin";
	private static final String DB_PASSWORD = "PJ4J3f";
	private static final String DB_DATABASE = "tcss";

	// as long as you're using mysql, leave this alone.
	private static final String DB_TYPE = "mysql";

	/** Max image size. */
	public static final long maxImageSize = 65536;
	
	/* ------------- SQL Variables ------------- */

	/** The SQL connection to the database */
	static Connection con;
	
	/**Plugin
	 * Gets a connection to for the manager and makes a good effort to make 
	 * sure it is open.
	 * 
	 * @return Either an open connection or null.
	 */
	static synchronized Connection getConnection() {
		try {
			if (con != null && con.isClosed())
			{
				con = null;
			}
		} catch (SQLException e) {
			con = null;
		}
		connect();
		return con;
	}
	
	/**
	 * Closes the database connection.
	 */
	public static void disconnect () {
		if (con == null) {
			return;
		}
		
		try {
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		con = null;
	}

	/**
	 * Connects to the database. To access the database, one must 
	 * first connect.
	 * 
	 * @return false should the connection fails; true otherwise.
	 */
	public static boolean connect() {
		// already connected.
		if (con != null) { return true; }

		// if you want to access and test against a Microsoft Access DB use following
		// instead of the connectMYSQL.
		
		return connectMYSQL();
		//return connectPrivately();
	}
	
	/** 
	 * Utility method to validate connection is valid.
	 * 
	 * @return true if DB is available; false otherwise.
	 */
	public static boolean isConnected() {
		if (con == null) { return false; }
		
		try {
			return !con.isClosed();
		} catch (SQLException e) {
			e.printStackTrace();
			
			return false;
		}
	}

	/**
	 * The database needs unique IDs so use this method to create them.
	 * <p>
	 * Note that the unique id is only statistically unique, but the odds are
	 * distinctly in our favor that there will not be duplicates in our lifetime.
	 * 
	 * @return unique ID string
	 */
	public static String createUniqueID() {
		// create unique ID
		UUID uid = UUID.randomUUID();
		return uid.toString();
	}	
	/**
	 * To access private MS-DB access database.
	 * 
	 * @return false should the connection fails; true otherwise.
	 */
	static boolean connectPrivately() {
		// already connected.
		if (con != null) { return true; }

		// Register the JDBC driver for MySQL. Simply accessing the class
		// will properly initialize everything.
		try {
			//Class.forName("com.mysql.jdbc.Driver");
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
		} catch (ClassNotFoundException cnfe) {
			System.err.println ("Make sure you Update your CompUnitEnvironment to retrieve required jdbc drivers...");
			return false;
		}

		//Define URL for database server 
		// NOTE: must fill in DATABASE NAME
		//String url =  "jdbc:" + DB_TYPE + "://" + DB_SERVER + "/" + DB_DATABASE;

		try {
			// Get a connection to the database for a
			// user with the given user name and password.
			// con = DriverManager.getConnection(url, DB_USER, DB_PASSWORD);
			con = DriverManager.getConnection(
				     "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=scratchDB.mdb"
				      );
			return true;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		}
	}
	
	/**
	 * To access remote MYSQL database.
	 * 
	 * @return false should the connection fails; true otherwise.
	 */
	static boolean connectMYSQL() {
		// already connected.
		if (con != null) { return true; }

		// Register the JDBC driver for MySQL. Simply accessing the class
		// will properly initialize everything.
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException cnfe) {
			System.err.println ("Make sure you Update your CompUnitEnvironment to retrieve required jdbc drivers...");
			return false;
		}

		//Define URL for database server 
		// NOTE: must fill in DATABASE NAME
		String url =  "jdbc:" + DB_TYPE + "://" + DB_SERVER + "/" + DB_DATABASE;

		try {
			// Get a connection to the database for a
			// user with the given user name and password.
			con = DriverManager.getConnection(url, DB_USER, DB_PASSWORD);
			return true;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		}
	}
	
	
	
	
	
	/**
	 * Retrieves icons from icons table.
	 * 
	 * @return A Collection<Icon> containing all known Icons.
	 * @throws IllegalArgumentException [If something happens...]
	 */
	public static Collection<Icon> getIcons() throws Exception {
		Connection con = getConnection();
		ArrayList<Icon> icons = new ArrayList<Icon>();
		
		try {
			PreparedStatement pstmt = con.prepareStatement("SELECT * FROM icons;");
			
			// Create the statement handler
			// Execute the SQL statement and store result into the ResultSet
			ResultSet result = pstmt.executeQuery();
			
			while (result.next()) {
				String name = result.getString("name");
				String url = result.getString("url");

				Icon icon = new Icon(name, new URL(url));
				icons.add(icon);
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
			throw new IllegalArgumentException ("Unable to get icons!");
		}
		
		return icons;
	}
	
	/**
	 * Adds an icon to the icons table.
	 * 
	 * @param name        The icon's name.
	 * @param urlLocation The URL of the icon.
	 * @return true on success.
	 * @throws IllegalArgumentException [on some condition...]
	 */
	public static boolean addIcon (String name, String urlLocation) throws Exception {
		Connection con = getConnection();
		
		try {
			PreparedStatement pstmt = con.prepareStatement(
				"INSERT into icons(name,url) VALUES(?,?);");
			pstmt.setString(1, name);
			pstmt.setString(2, urlLocation);
			
			// Create the statement handler
			// Execute the SQL statement and store result into the ResultSet
			pstmt.executeUpdate();
			
			int numInserted = pstmt.getUpdateCount();
			if (numInserted == 0) {
				throw new IllegalArgumentException ("Unable to insert " + name + ".");
			}
		} catch (SQLException e) {
			throw new IllegalArgumentException (e.getMessage());
		}
		
		return true;
	}
	
	/**
	 * Deletes an icon from the icons table.
	 * 
	 * @param name The icon's name.
	 * @return true on success.
	 * @throws IllegalArgumentException [on some condition...]
	 */
	public static boolean deleteIcon (String name) throws Exception {
		Connection con = getConnection();
		
		try {
			PreparedStatement pstmt = con.prepareStatement(
				"DELETE from icons where name = ?");
			pstmt.setString(1, name);
			
			// Create the statement handler
			// Execute the SQL statement and store result into the ResultSet
			pstmt.executeUpdate();
			
		} catch (SQLException e) {
			throw new IllegalArgumentException (e.getMessage());
		}
		
		return true;
	}
	
	
	
	
	
	/**
	 * Retrieve users from users table.
	 * 
	 * @return A Collection<User> containing all the users in the users table.
	 * @throws IllegalArgumentException [on some condition...]
	 */
	public static Collection<User> getUsers() throws Exception {
		Connection con = getConnection();
		ArrayList<User> users = new ArrayList<User>();
		
		try {
			PreparedStatement pstmt = con.prepareStatement("SELECT * FROM users;");
			
			// Create the statement handler
			// Execute the SQL statement and store result into the ResultSet
			ResultSet result = pstmt.executeQuery();
			
			while (result.next()) {
				User user = new User(result.getString("username"), result.getString("password"));
				user.setAdmin(result.getBoolean("isAdmin"));
				user.setBlocked(result.getBoolean("isBlocked"));
				user.setGamesLost(result.getInt("gamesLost"));
				user.setGamesWon(result.getInt("gamesWon"));
				user.setIconName(result.getString("iconName"));
				user.setLastLogin(result.getLong("lastLogin"));
				user.setRating(result.getInt("rating"));
				if (result.getInt("isBlocked") == 1)
					user.setBlocked(true);
				else
					user.setBlocked(false);
				
				users.add(user);
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
			throw new IllegalArgumentException ("Unable to get users!");
		}
		
		return users;
	}
	
	/**
	 * Add user to users table.
	 * 
	 * @param u An instance of the user class to add to the database.
	 * @return true on success; false if user already exists.
	 * @throws IllegalArgumentException on SQLException
	 * @throws IllegalArgumentException if User not inserted into table
	 */
	public static boolean addUser (User u) throws Exception {
		Connection con = getConnection();
		
		// Add new user to database
		try {
			PreparedStatement pstmt2 = con.prepareStatement(
				"INSERT into users(username, password) VALUES(?,?);");
			
			pstmt2.setString(1, u.getName());
			pstmt2.setString(2, u.getEncPwd());
			
			// Create the statement handler
			// Execute the SQL statement and store result into the ResultSet
			pstmt2.executeUpdate();
			
			int numInserted = pstmt2.getUpdateCount();
			if (numInserted == 0) {
				throw new IllegalArgumentException ("Unable to insert " + u.getName() + ".");
			}
		} catch (SQLException e) {
			throw new IllegalArgumentException (e.getMessage());
		}
		
		try {
			return updateUser(u);
		} catch (IllegalArgumentException e) {
			throw new IllegalArgumentException (e.getMessage());
		}
	}
	
	/**
	 * Delete user from users table.
	 * 
	 * @param username
	 * @return true on success
	 * @throws IllegalArgumentException on SQLException
	 */
	public static boolean deleteUser (String username) throws Exception {
		Connection con = getConnection();
		
		try {
			PreparedStatement pstmt = con.prepareStatement(
				"DELETE from users where username = ?");
			pstmt.setString(1, username);
			
			// Create the statement handler
			// Execute the SQL statement and store result into the ResultSet
			pstmt.executeUpdate();
		} catch (SQLException e) {
			throw new IllegalArgumentException (e.getMessage());
		}
		
		return true;
	}

	/**
	 * Updates the given User's information in the table.
	 * 
	 * @param user An instance of the User class that needs to have its data
	 *             updated in the users table.
	 * @return true on success.
	 * @throws IllegalArgumentException on SQLException
	 */
	public static boolean updateUser (User u) throws Exception {
		Connection con = getConnection();
		
		try {
			PreparedStatement pstmt = con.prepareStatement(
				"UPDATE users SET password=?, isAdmin=?, gamesLost=?, gamesWon=?, iconName=?, lastLogin=?, rating=?, isBlocked=? WHERE username=?;");
			pstmt.setString(1, u.getEncPwd());
			pstmt.setBoolean(2, u.isAdmin());
			pstmt.setInt(3, u.getGamesLost());
			pstmt.setInt(4, u.getGamesWon());
			pstmt.setString(5, u.getIconName());
			pstmt.setLong(6, u.getLastLogin());
			pstmt.setInt(7, u.getRating());
			if (u.isBlocked())
				pstmt.setInt(8, 1);
			else
				pstmt.setInt(8, 0);
			
			pstmt.setString(9, u.getName());
			
			// Create the statement handler
			// Execute the SQL statement and store result into the ResultSet
			pstmt.executeUpdate();
		} catch (SQLException e) {
			throw new IllegalArgumentException (e.getMessage());
		}
		return true;
	}
	
	
	
	
	
	/**
	 * Retrieve plugins from plugins table.
	 * 
	 * @return Collection<Plugin> containing all the plugins.
	 * @throws Exception [fill in...]
	 */
	public static Collection<Plugin> getPlugins() throws Exception {
		Connection con = getConnection();
		ArrayList<Plugin> plugins = new ArrayList<Plugin>();
		
		try {
			PreparedStatement pstmt = con.prepareStatement("SELECT * FROM plugins;");
			
			// Create the statement handler
			// Execute the SQL statement and store result into the ResultSet
			ResultSet result = pstmt.executeQuery();
			
			while (result.next()) {
				String name = result.getString("name");
				String url = result.getString("url");
				String status = result.getString("suspended");

				Plugin plugin = new Plugin(name, new URL(url));
				
				if (status.equals("T"))
					plugin.suspend();
				else
					plugin.unsuspend();
				
				plugins.add(plugin);
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
			throw new IllegalArgumentException ("Unable to get icons!");
		}
		
		return plugins;
	}
	
	/**
	 * Add plugin to plugins table.
	 * 
	 * @param name        The name of the plugin.
	 * @param urlLocation The url of the plugin.
	 * @return [fill in...]
	 * @throws Exception [fill in...]
	 */
	public static boolean addPlugin (String name, String urlLocation) throws Exception {
		Connection con = getConnection();
		
		try {
			PreparedStatement pstmt = con.prepareStatement(
				"INSERT into plugins(name,url) VALUES(?,?);");
			pstmt.setString(1, name);
			pstmt.setString(2, urlLocation);
			
			// Create the statement handler
			// Execute the SQL statement and store result into the ResultSet
			pstmt.executeUpdate();
			
			int numInserted = pstmt.getUpdateCount();
			if (numInserted == 0) {
				throw new IllegalArgumentException ("Unable to insert " + name + ".");
			}
		} catch (SQLException e) {
			throw new IllegalArgumentException (e.getMessage());
		}
		
		return true;
	}
	
	/**
	 * Delete plugin from plugins table.
	 * 
	 * @param name The name of the plugin.
	 * @return [fill in...]
	 * @throws Exception [fill in...]
	 */
	public static boolean deletePlugin (String name) throws Exception {
		Connection con = getConnection();
		
		try {
			PreparedStatement pstmt = con.prepareStatement(
				"DELETE from plugins where name = ?");
			pstmt.setString(1, name);
			
			// Create the statement handler
			// Execute the SQL statement and store result into the ResultSet
			pstmt.executeUpdate();
		} catch (SQLException e) {
			throw new IllegalArgumentException (e.getMessage());
		}
		
		return true;
	}
	
	
	public static boolean updatePlugin (Plugin p) throws Exception {
		Connection con = getConnection();
		
		try {
			PreparedStatement pstmt = con.prepareStatement(
				"UPDATE plugins SET wins=?, losses=?, suspended=? WHERE name=?;");
			pstmt.setInt(1, p.getWins());
			pstmt.setInt(2, p.getLosses());
			pstmt.setBoolean(3, p.getSuspended());
			pstmt.setString(4, p.getName());
			
			// Create the statement handler
			// Execute the SQL statement and store result into the ResultSet
			pstmt.executeUpdate();
		} catch (SQLException e) {
			throw new IllegalArgumentException (e.getMessage());
		}
		return true;
	}
}
